﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Text;
using System.Data;
using Microsoft.Reporting.WebForms;

public partial class ItemMasterToSaleCategory : System.Web.UI.Page
{
    private SqlConnection con = DBTools.getDBConnection();
    private SqlDataAdapter sda;

    protected void Page_Load(object sender, EventArgs e)
    {
        string sql_str = "select  A.[Code] as [Organization_Code], A1.[Name] as [Organization_Name] from  Base_Organization as A  left join [Base_Organization_Trl] as A1 on (A1.SysMlFlag = 'zh-CN') and (A.[ID] = A1.[ID])";
        sda = new SqlDataAdapter(sql_str, con);
        DataTable dt = new DataTable();
        sda.Fill(dt);
        DropDownList1.DataTextField = "Organization_Name";
        DropDownList1.DataValueField = "Organization_Code";
        DropDownList1.DataSource = dt.DefaultView;
        DropDownList1.DataBind();
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string org = DropDownList1.SelectedValue.Trim();
        string itemmaster = "1=1";
        if (TextBox1.Text.Trim() != null && TextBox1.Text.Trim() != "")
        {
            itemmaster = "A.[Code] ='" + TextBox1.Text.Trim() + "'";
        }

        StringBuilder sql = new StringBuilder();

        sql.AppendLine(" SELECT                                                                                     ");
        sql.AppendLine("  A4.dlbm as 大类编码,                                                                                  ");
        sql.AppendLine("  A4.dlmc as 大类名称,                                                                                  ");
        sql.AppendLine("  A2.Code AS 小类编码,                                                                          ");
        sql.AppendLine("  A3.Name AS 小类名称,                                                                          ");
        sql.AppendLine("  A.Code as 料号,                                                                                   ");
        sql.AppendLine("  A.Name as 品名,                                                                                   ");
        sql.AppendLine("  A.DescFlexField_PubDescSeg1 AS 型号,                                                        ");
        sql.AppendLine("  A.SPECS as 规格                                                                                   ");
        sql.AppendLine(" FROM dbo.CBO_ItemMaster AS A LEFT OUTER                                                    ");
        sql.AppendLine(" JOIN dbo.Base_Organization AS A1 ON A.Org = A1.ID LEFT OUTER                               ");
        sql.AppendLine(" JOIN dbo.CBO_Category AS A2 ON A.SaleCategory = A2.ID LEFT OUTER                           ");
        sql.AppendLine(" JOIN dbo.CBO_Category_Trl AS A3 ON A3.SysMLFlag = 'zh-CN' AND A2.ID = A3.ID LEFT OUTER     ");
        sql.AppendLine(" JOIN (                                                                                     ");
        sql.AppendLine(" SELECT A.Code AS dlbm,                                                                     ");
        sql.AppendLine("  A1.Name AS dlmc,                                                                          ");
        sql.AppendLine("  A2.Code AS ItemCategory_CategorySystem_Code,                                              ");
        sql.AppendLine("  A3.Code AS ItemCategory_Org_Code                                                          ");
        sql.AppendLine(" FROM dbo.CBO_Category AS A LEFT OUTER                                                      ");
        sql.AppendLine(" JOIN dbo.CBO_Category_Trl AS A1 ON A1.SysMLFlag = 'zh-CN' AND A.ID = A1.ID LEFT OUTER      ");
        sql.AppendLine(" JOIN dbo.CBO_CategoryType AS A2 ON A.CategorySystem = A2.ID LEFT OUTER                     ");
        sql.AppendLine(" JOIN dbo.Base_Organization AS A3 ON A.Org = A3.ID                                          ");
        sql.AppendLine(" WHERE (A2.Code = '3') AND (A3.Code = '" + org + "')) AS A4 ON SUBSTRING(A2.Code, 1, 4) = A4.dlbm   ");
        sql.AppendLine(" WHERE (A1.Code = '" + org + "') AND (A.Effective_IsEffective = 1) and " + itemmaster + "   ");
        if (!CheckBox1.Checked)
        {
            sql.AppendLine(" and A2.Code is not null");
        }
        sql.AppendLine(" order by  A2.Code" );

        sda.SelectCommand.CommandText = sql.ToString();
        DataTable dt1 = new DataTable();
        sda.Fill(dt1);
        this.ReportViewer1.LocalReport.DataSources.Clear();
        ReportDataSource rds = new ReportDataSource("DataSet1", dt1);
        this.ReportViewer1.LocalReport.DataSources.Add(rds);
        this.ReportViewer1.LocalReport.Refresh();
    }
}